import requests
import pandas as pd
import datetime
from sklearn.preprocessing import MinMaxScaler
import pymysql
from joblib import dump

class WeatherUtils(object):
    def __init__(self):
        self.date_list = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01'
        ]
        self.url = 'http://v1.yiketianqi.com/api'

    
    def get_data(self): 
        data_list = []
    for d in self.date_list:
        conf ={
            'appid':'88249599',#使用自己注册的appid 是string 用户appid注册开发账号 
            'appsecret':'BA7zIjrM',#是string 用户appsecret
            'version':'history',#是string 接口版本标识 固定值：history每个接口的version值都不一样 
            'year':d[:4],#是 string 年份 如：2015 
            'month':d[5:7],#是 string 月份 如：5 
            'city':'南昌'#否 string 城市名称 不要带市和区；如：青岛、铁西 
        } 
        #发起请求获取数据
        res=requests.get(self.url +'?', params=conf) 
        res_data= res.json() 
        for i in res_data['data']: 
            data_list.append({ 
                'date': datetime.datetime.strptime(i['ymd'],'%Y-%m-%d'), 
                'bwendu': i['bWendu'], 
                'yWendu': i['yWendu'], 
                'tianqi': i['tianqi'], 
                'fengli': i['fengli'], 
            }) 
        df = pd.DataFrame(data_list) 
        df.to_csv('./timing/weather.csv')

class MysqlUtils(object):

    def __init(self):
        self.conn=pymysql.connect(
            host='127.0.0.1',
             user='root',
            password='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )
        self.weather_data = pd.read_csv('./timing/weather.csv')

    def is_holiday(self, date):
        """是否节假日判断"""
        if date in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03']:
            return 1
        return 0

    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date, count(*) as count
        FROM order_user_gate_rel g 
        WHERE DATE(g.create_time) < '2025-01-01' 
        GROUP BY date
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)

        # 合并天气数据
        self.weather_data['date'] = pd.to_datetime(self.weather_data['date'])
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(self.weather_data, df, on='date')
        df_pivot.set_index('date', inplace=True)
        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几 (0-6)
        df_pivot['month'] = df_pivot.index.month  # 月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        print(df_pivot.head())
        #对星期几和月份进行热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int)
        # 对温度进行类型转换
        df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('0', '').astype(int)
        df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('0', '').astype(int)

        print(df_pivot)
        # 归一化入园人数
        scaler = MinMaxScaler()
        features = df_pivot[['count']]
        df_pivot['count'] = scaler.fit_transform(features)
        dump(scaler, 'timing/scaler.joblib')

        # 归一化温度
        weather_features = df_pivot[['bWendu', 'yWendu']]
        df_pivot[['bWendu', 'yWendu']] = scaler.fit_transform(weather_features)
        dump(scaler, 'NN/weather_scaler.joblib')
        #print(df_pivot.head)

        df_pivot.to_csv('timing/scenic_data.csv', index=False)

if __name__=='__main__':
    mu=MysqlUtils()
    mu.get_scenic_data